/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pa036is.daoimplementation;

import cz.muni.fi.pa036is.daointerface.RoundDAO;
import cz.muni.fi.pa036is.entity.Match;
import cz.muni.fi.pa036is.entity.Round;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Repository;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Kuba
 */
@Repository
public class RoundDAOImpl implements RoundDAO {

    private Connection con = null;
    private Statement st = null;
    private ResultSet rs = null;

    public RoundDAOImpl() throws ClassNotFoundException {



        String url = "jdbc:postgresql://localhost/PA036";
        String user = "postgres";
        String password = "test";

        try {
            Class.forName("org.postgresql.Driver");
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void addRound(Round round) {
        try {

            String insert = "INSERT INTO round"
                    + "(match_id, sequence_number, point_count1, point_count2) VALUES"
                    + "(?,?,?,?)";
            PreparedStatement preparedStatement = con.prepareStatement(insert);


            preparedStatement.setLong(1, round.getMatchId());
            preparedStatement.setInt(2, round.getSequenceNumber());
            preparedStatement.setInt(3, round.getPointCount1());
            preparedStatement.setInt(4, round.getPointCount2());

            preparedStatement.execute();

        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void editRound(Round round) {
        try {

            String update = "UPDATE round SET point_count1= ?, point_count2= ? WHERE match_id= ? AND sequence_number= ?";

            PreparedStatement preparedStatement = con.prepareStatement(update);

            preparedStatement.setInt(1, round.getPointCount1());
            preparedStatement.setInt(2, round.getPointCount2());
            preparedStatement.setLong(3, round.getMatchId());
            preparedStatement.setInt(4, round.getSequenceNumber());

            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    

    @Override
    public void deleteRound(Round round) {
        try {

            String delete = "DELETE FROM round WHERE match_id= ? AND sequence_number= ?";

            PreparedStatement preparedStatement = con.prepareStatement(delete);

            preparedStatement.setLong(1, round.getMatchId());
            preparedStatement.setInt(2, round.getSequenceNumber());

            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Round> getAllRoundsForMatch(Long id) {
        List<Round> roundList = new ArrayList<Round>();
        try {

            String getAll = "SELECT * FROM round WHERE match_id = ? ORDER BY sequence_number";

            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            preparedStatement.setLong(1, id);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Round round = new Round();
                round.setMatchId(rs.getLong(1));
                round.setPointCount1(rs.getInt(2));
                round.setPointCount2(rs.getInt(3));
                round.setSequenceNumber(rs.getInt(4));
                roundList.add(round);

            }
        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return roundList;
    }

    @Override
    public List<Round> getAllRounds() {
        List<Round> roundList = new ArrayList<Round>();

        try {

            String getAll = "SELECT * FROM round";

            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                Round round = new Round();
                round.setMatchId(rs.getLong(1));
                round.setSequenceNumber(rs.getInt(2));
                round.setPointCount1(rs.getInt(3));
                round.setPointCount2(rs.getInt(4));
                roundList.add(round);

            }
        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return roundList;
    }

    @Override
    public Round findRound(Long matchId, int sequenceNumber) {
        Round round = new Round();
        try {

            String getAll = "SELECT * FROM round WHERE match_id = ? AND sequence_number = ?";

            PreparedStatement preparedStatement = con.prepareStatement(getAll);

            preparedStatement.setLong(1, matchId);
            preparedStatement.setLong(2, sequenceNumber);

            rs = preparedStatement.executeQuery();

            rs.next();
            round.setMatchId(rs.getLong(1));
            round.setPointCount1(rs.getInt(2));
            round.setPointCount2(rs.getInt(3));
            round.setSequenceNumber(rs.getInt(4));


        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return round;
    }

    @Override
    public void deleteRound(Long matchId, int sequenceNumber) {
        try {

            String delete = "DELETE FROM round WHERE match_id= ? AND sequence_number = ? ";

            PreparedStatement preparedStatement = con.prepareStatement(delete);

            preparedStatement.setLong(1, matchId);
            preparedStatement.setInt(2, sequenceNumber);

            preparedStatement.executeUpdate();
            
            // viem ze to je blbo spravene, no je to robene na rychlo:
            List<Round> rounds = this.getAllRoundsForMatch(matchId);
            for (Round r: rounds) {
                this.deleteRound(r);
            }
            
            // prepocitam kola
            int seqNum = 1;
            for (Round r: rounds) {
                r.setSequenceNumber(seqNum++);
                this.addRound(r);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(RoundDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private int getFinalScoreForPlayer(Match match, int playerIndex) {
        try {

            String getScore = "SELECT SUM(point_count" + playerIndex + ") FROM round WHERE match_id = ?";

            PreparedStatement preparedStatement = con.prepareStatement(getScore);

            preparedStatement.setLong(1, match.getId());

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                return rs.getInt(1);
            }

            return 0;
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return 0;
    }

    @Override
    public int getFinalScoreForPlayer1(Match match) {
        return this.getFinalScoreForPlayer(match, 1);
    }

    @Override
    public int getFinalScoreForPlayer2(Match match) {
        return this.getFinalScoreForPlayer(match, 2);
    }

    @Override
    public int getNextIndexForMatch(Long id) {
        try {

            String getScore = "SELECT MAX(sequence_number) FROM round WHERE match_id = ?";

            PreparedStatement preparedStatement = con.prepareStatement(getScore);

            preparedStatement.setLong(1, id);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                return rs.getInt(1) + 1;
            }

            return 0;
        } catch (SQLException ex) {
            Logger.getLogger(MatchDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return 0;
    }
}
